package com.itany.corejava.code13_jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author MissChen
 * @date 2024年03月22日 16:28
 * 创建两张表
 * 班级表:主键自增长、班级名称(唯一的)、所属专业
 *          id、name、major
 *
 * 学生表:主键自增长、学号、姓名、年龄、所属班级(外键)
 *          id、no、name、age、class_id
 *
 * 1.保存班级以及该班中的所有学生信息
 * 2.根据班级名称查找指定班级以及该班级中所有学生信息
 * 3.查询所有学生以及学生对应所属班级信息
 */
public class Test12_练习 {
    public static void main(String[] args) throws SQLException, DataAccessException {
        // ClassBean classBean=new ClassBean("wbs24032","计算机");
        // List<Student> students=new ArrayList<>();
        // Student student1=new Student("0315","张1",20);
        // Student student2=new Student("0316","张2",22);
        // Student student3=new Student("0317","李3",23);
        // Student student4=new Student("0318","张4",20);
        // students.add(student1);
        // students.add(student2);
        // students.add(student3);
        // students.add(student4);
        // insertClassAndStu(classBean,students);


        // ClassBean classBean=findClassByName("wbs24032");
        // System.out.println(classBean);

        List<Student> students=findAllStus();
        for(Student student:students){
            System.out.println(student);
        }

    }

    /**
     * 保存班级以及该班中的所有学生信息
     * @param classBean 添加的班级
     * @param students 在该班级中所添加的学生的集合
     * @throws SQLException
     * @throws DataAccessException
     */
    public static void insertClassAndStu(ClassBean classBean, List<Student> students) throws SQLException, DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Integer id=null;
        try {
            conn=JDBCUtil.getConnection();
            conn.setAutoCommit(false);
            String sql=new StringBuffer()
                    .append(" insert into ")
                    .append(" t_class ")
                    .append(" (name,major) ")
                    .append(" values  ")
                    .append(" (?,?) ")
                    .toString();
            ps=conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1,classBean.getName());
            ps.setString(2,classBean.getMajor());
            ps.executeUpdate();
            rs=ps.getGeneratedKeys();
            while(rs.next()){
                id=rs.getInt(1);
            }
            classBean.setId(id);
            sql=new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student ")
                    .append(" (no,name,age,class_id) ")
                    .append(" values  ")
                    .append(" (?,?,?,?) ")
                    .toString();
            ps=conn.prepareStatement(sql);
            for(Student student:students){
                student.setClassBean(classBean);
                ps.setString(1,student.getNo());
                ps.setString(2,student.getName());
                ps.setInt(3,student.getAge());
                ps.setInt(4,student.getClassBean().getId());
                ps.executeUpdate();
            }
            conn.commit();
        } catch (DataAccessException e) {
            conn.rollback();
            throw e;
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }finally {
            JDBCUtil.close(conn,ps,rs);
        }
    }

    /**
     * 根据班级名称查找指定班级以及该班级中所有学生信息
     * @param name
     * @return
     */
    public static ClassBean findClassByName(String name) throws SQLException, DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Integer id=null;
        ClassBean classBean=null;
        try {
            conn=JDBCUtil.getConnection();
            conn.setAutoCommit(false);
            String sql=new StringBuffer()
                    .append(" select c.id,c.name,c.major,s.id,s.no,s.name,s.age ")
                    .append(" from t_class c left join t_student s ")
                    .append(" on c.id=s.class_id  ")
                    .append(" where c.name=?  ")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setString(1,name);
            rs=ps.executeQuery();
            while(rs.next()){
                if(classBean==null){
                    classBean=new ClassBean();
                    classBean.setId(rs.getInt("c.id"));
                    classBean.setName(rs.getString("c.name"));
                    classBean.setMajor(rs.getString("c.major"));
                }
                Student student=new Student();
                student.setId(rs.getInt("s.id"));
                student.setNo(rs.getString("s.no"));
                student.setName(rs.getString("s.name"));
                student.setAge(rs.getInt("s.age"));
                classBean.addStu(student);
            }
            conn.commit();
            return classBean;
        } catch (DataAccessException e) {
            conn.rollback();
            throw e;
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }finally {
            JDBCUtil.close(conn,ps,rs);
        }
    }

    /**
     * 查询所有学生以及学生对应所属班级信息
     * @return
     */
    public static List<Student> findAllStus() throws SQLException, DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<Student> students=new ArrayList<>();
        try {
            conn=JDBCUtil.getConnection();
            conn.setAutoCommit(false);
            String sql=new StringBuffer()
                    .append(" select s.id,s.no,s.name,s.age,c.id,c.name,c.major  ")
                    .append(" from t_student s left join t_class c ")
                    .append(" on c.id=s.class_id  ")
                    .toString();
            ps=conn.prepareStatement(sql);

            rs=ps.executeQuery();
            while(rs.next()){

                Student student=new Student();
                student.setId(rs.getInt("s.id"));
                student.setNo(rs.getString("s.no"));
                student.setName(rs.getString("s.name"));
                student.setAge(rs.getInt("s.age"));
                ClassBean classBean=new ClassBean();
                classBean.setId(rs.getInt("c.id"));
                classBean.setName(rs.getString("c.name"));
                classBean.setMajor(rs.getString("c.major"));
                student.setClassBean(classBean);
                students.add(student);
            }
            conn.commit();
            return students;

        } catch (DataAccessException e) {
            conn.rollback();
            throw e;
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }finally {
            JDBCUtil.close(conn,ps,rs);
        }
    }
}
